
alter      trigger tft_mssub_bi on dbo.tft_mssub
instead of insert
as
begin
  set nocount on

  declare  @nbalyear integer   
  declare  @nbalmonth integer   
   
  declare  @bmbala decimal(38,8)   
  declare  @bysumdebit decimal(38,8)   
  declare  @bysumcredit decimal(38,8)   
  declare  @tmsumdebit decimal(38,8)   
  declare  @tmsumcredit decimal(38,8)   
  declare  @tmsum decimal(38,8)   
   
  declare  @fbmbala decimal(38,8)   
  declare  @fbysumdebit decimal(38,8)   
  declare  @fbysumcredit decimal(38,8)   
  declare  @ftmsumdebit decimal(38,8)   
  declare  @ftmsumcredit decimal(38,8)   
  declare  @ftmsum decimal(38,8)   
   
  declare  @bmqtybala decimal(38,8)   
  declare  @byqtysumcredit decimal(38,8)   
  declare  @byqtysumdebit decimal(38,8)   
  declare  @tmqtysumcredit decimal(38,8)   
  declare  @tmqtysumdebit decimal(38,8)   
   
  declare  @nsubjectid integer
  declare  @nyear integer   
  declare  @nmonth integer   
  declare  @nfilid integer   
  declare  @nmoneyid integer   
  declare  @nbmbala decimal(38,8)   
  declare  @nbysumdebit decimal(38,8)   
  declare  @nbysumcredit decimal(38,8)   
  declare  @ntmsumdebit decimal(38,8)   
  declare  @ntmsumcredit decimal(38,8)   
  declare  @ntmsum decimal(38,8)   
  declare  @nfbmbala decimal(38,8)   
  declare  @nfbysumdebit decimal(38,8)   
  declare  @nfbysumcredit decimal(38,8)   
  declare  @nftmsumdebit decimal(38,8)   
  declare  @nftmsumcredit decimal(38,8)   
  declare  @nftmsum decimal(38,8)   
  declare  @nbmqtybala decimal(38,8)   
  declare  @nbyqtysumcredit decimal(38,8)   
  declare  @nbyqtysumdebit decimal(38,8)   
  declare  @ntmqtysumcredit decimal(38,8)   
  declare  @ntmqtysumdebit decimal(38,8)   
  declare  @nodata integer   

  declare @local_tab table(
	[subjectid] int  not null ,
	[nyear] int not null ,
	[nmonth] int not null ,
	[filid] int not null ,
        [moneyid] int not null,
	[bmbala]    decimal(38,8),
	[bysumdebit]   decimal(38,8),
	[bysumcredit]  decimal(38,8),
	[tmsumdebit]  decimal(38,8),
	[tmsumcredit]   decimal(38,8),
	[tmsum]  decimal(38,8),
	[fbmbala]   decimal(38,8),
	[fbysumdebit]   decimal(38,8),
	[fbysumcredit]   decimal(38,8),
	[ftmsumdebit]   decimal(38,8),
	[ftmsumcredit]  decimal(38,8),
	[ftmsum]  decimal(38,8),
	[bmqtybala]  decimal(38,8),
	[byqtysumdebit]  decimal(38,8) ,
	[byqtysumcredit]  decimal(38,8),
	[tmqtysumdebit]  decimal(38,8),
	[tmqtysumcredit]  decimal(38,8) ,
        [odata] int
)

  insert into @local_tab select * from inserted
  declare cur_tft_mssub cursor for select * from @local_tab 
  open cur_tft_mssub 
  fetch next from  cur_tft_mssub
  into  @nsubjectid, @nyear, @nmonth, @nfilid,@nmoneyid, @nbmbala , @nbysumdebit, @nbysumcredit,
        @ntmsumdebit,@ntmsumcredit,@ntmsum, @nfbmbala, @nfbysumdebit, @nfbysumcredit,
        @nftmsumdebit , @nftmsumcredit, @nftmsum, @nbmqtybala, @nbyqtysumcredit,
        @nbyqtysumdebit, @ntmqtysumcredit, @ntmqtysumdebit ,@nodata
  while @@fetch_status = 0 
  begin    
    set  @nbalyear=NULL
    set  @nbalmonth=NULL
    set  @bmbala = 0
    set  @bysumdebit = 0
    set  @bysumcredit = 0
    set  @tmsumdebit = 0
    set  @tmsumcredit = 0
    set  @tmsum = 0
    set  @fbmbala = 0
    set  @fbysumdebit = 0
    set  @fbysumcredit = 0
    set  @ftmsumdebit = 0
    set  @ftmsumcredit = 0
    set  @ftmsum = 0

    select @nbalyear = nbalyear,
           @nbalmonth = nbalmonth,
           @bmbala = bmbala,
           @bysumdebit = bysumdebit,
           @bysumcredit = bysumcredit,
           @tmsumdebit = tmsumdebit,
           @tmsumcredit = tmsumcredit,
           @tmsum = tmsum,
           @fbmbala = fbmbala,
           @fbysumdebit = fbysumdebit,
           @fbysumcredit = fbysumcredit,
           @ftmsumdebit = ftmsumdebit,
           @ftmsumcredit = ftmsumcredit,
           @ftmsum = ftmsum
      from f_mssubgetpriorinfo(@nmoneyid,@nsubjectid,@nyear,@nmonth,@nfilid)

    if ( @bmbala is null)       SET @bmbala =0
    if ( @bysumcredit is null)  SET @bysumcredit =0
    if ( @bysumdebit is null)   SET @bysumdebit =0
    if ( @tmsumcredit is null)  SET @tmsumcredit =0
    if ( @tmsumdebit is null)   SET @tmsumdebit =0
    if ( @fbmbala is null)      SET @fbmbala =0
    if ( @fbysumcredit is null or @nmonth=1) SET @fbysumcredit =0
    if ( @fbysumdebit is null or @nmonth=1)  SET @fbysumdebit =0
    if ( @ftmsumcredit is null)              SET @ftmsumcredit =0
    if ( @ftmsumdebit is null)               SET @ftmsumdebit =0

    SET @nbysumcredit=@tmsumcredit + @bysumcredit
    SET @nbysumdebit =@tmsumdebit  + @bysumdebit 
    SET @nfbysumcredit=@ftmsumcredit + @fbysumcredit
    SET @nfbysumdebit =@ftmsumdebit  + @fbysumdebit 

    if (@nyear<>@nbalyear) 
    begin
      SET @nbysumcredit=0
      SET @nbysumdebit=0
      SET @nfbysumcredit=0
      SET @nfbysumdebit=0
    end
     
    SET @nbmbala= @bmbala+@tmsumdebit-@tmsumcredit
    SET @nfbmbala=@fbmbala+@ftmsumdebit-@ftmsumcredit
    insert into tft_mssub values( @nsubjectid, @nyear, @nmonth, @nfilid,@nmoneyid, isnull(@nbmbala,0) , isnull(@nbysumdebit,0),
                                isnull(@nbysumcredit,0), isnull(@ntmsumdebit,0),isnull(@ntmsumcredit,0),isnull(@ntmsum,0), isnull(@nfbmbala,0),
                                isnull(@nfbysumdebit,0),isnull(@nfbysumcredit,0),isnull(@nftmsumdebit,0) ,isnull(@nftmsumcredit,0), 
                                isnull(@nftmsum,0), isnull(@nbmqtybala,0), isnull(@nbyqtysumcredit,0),
                                isnull(@nbyqtysumdebit,0), isnull(@ntmqtysumcredit,0), isnull(@ntmqtysumdebit,0) ,@nodata)
   fetch next from cur_tft_mssub
   into @nsubjectid, @nyear, @nmonth, @nfilid, @nmoneyid,@nbmbala , @nbysumdebit, @nbysumcredit,
        @ntmsumdebit, @ntmsumcredit, @ntmsum, @nfbmbala, @nfbysumdebit, @nfbysumcredit,
        @nftmsumdebit, @nftmsumcredit, @nftmsum, @nbmqtybala, @nbyqtysumcredit,
        @nbyqtysumdebit, @ntmqtysumcredit, @ntmqtysumdebit,@nodata
   end
   close cur_tft_mssub
   deallocate cur_tft_mssub
end

